﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ChurchDiary.DAL.DAL;
using ChurchDiary.DAL.Contract;
using System.Data.Common;
using System.Data;
using ChurchDiary.TableEntity;
using ChurchDiary.Common;

namespace ChurchDiary.DAL.Methods
{
    public class DAL_Common : DataAccess, IDALCommon
    {
        public List<EntityCountry> GetCountry()
        {
            List<EntityCountry> countries = new List<EntityCountry>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_CountryState]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Countryid", DbType.Guid, null);
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.Int32, 1);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return countries;
                            }
                            while (OReader.Read())
                            {
                                EntityCountry Country = new EntityCountry();
                                Country.CountryName = OReader["CountryName"].ToString();
                                Country.CountryId = (Guid)OReader["CountryId"];
                                countries.Add(Country);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return countries;
        }
        public List<EntityStates> GetState(Guid CountryId)
        {
            List<EntityStates> states = new List<EntityStates>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_CountryState]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Countryid", DbType.Guid, CountryId);
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.Int32, 2);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return states;
                            }
                            while (OReader.Read())
                            {
                                EntityStates state = new EntityStates();
                                state.StateName = OReader["StateName"].ToString();
                                state.StateId = (Guid)OReader["StateId"];
                                states.Add(state);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return states;
        }
        #region Email
        public List<EntityEmail> GetEmails()
        {
            List<EntityEmail> Emails = new List<EntityEmail>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Email]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.Guid, GETMODE.LIST);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Emails;
                            }
                            while (OReader.Read())
                            {
                                EntityEmail Email = new EntityEmail();
                                Email.EmailId = (Guid)OReader["EmailId"];
                                Email.EmailFrom = OReader["EmailFrom"].ToString();
                                Email.EmailToAddress = OReader["EmailToAddress"].ToString();
                                Email.EmailBcc = OReader["EmailBcc"].ToString();
                                Email.EmailCC = OReader["EmailCC"].ToString();
                                Email.EmailSubject = OReader["EmailSubject"].ToString();
                                Email.EmailContent = OReader["EmailContent"].ToString();
                                Email.EmailType = OReader["EmailType"].ToString();
                                Email.EmailCreatedBy = (Guid)OReader["EmailCreatedBy"];
                                Email.EmailCreatedOn = (DateTime)OReader["EmailCreatedOn"];
                                Email.EmaiStatus = (Int32)OReader["EmaiStatus"];
                                Email.EmailSendOn = (DateTime)OReader["EmailSendOn"];
                                Emails.Add(Email);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Emails;
        }
        public EntityEmail GetEmailsByUserId(Guid userId,String emailType)
        {
            EntityEmail Email = new EntityEmail();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Email]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "USER");
                    SQLServer.AddInParameter(dbCommand, "@EmailId", DbType.Guid, userId);
                    SQLServer.AddInParameter(dbCommand, "@EmailType", DbType.String, emailType);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Email;
                            }
                            while (OReader.Read())
                            {
                                Email.EmailId = (Guid)OReader["EmailId"];
                                Email.EmailFrom = OReader["EmailFrom"].ToString();
                                Email.EmailToAddress = OReader["EmailToAddress"].ToString();
                                Email.EmailBcc = OReader["EmailBcc"].ToString();
                                Email.EmailCC = OReader["EmailCC"].ToString();
                                Email.EmailSubject = OReader["EmailSubject"].ToString();
                                Email.EmailContent = OReader["EmailContent"].ToString();
                                Email.EmailType = OReader["EmailType"].ToString();
                                Email.EmailCreatedBy = (Guid)OReader["EmailCreatedBy"];
                                Email.EmailCreatedOn = (DateTime)OReader["EmailCreatedOn"];
                                Email.EmaiStatus = (Int32)OReader["EmaiStatus"];
                                Email.EmailSendOn = (DateTime)OReader["EmailSendOn"];
                                Email.EmailSourceId = (Guid)OReader["EmailSourceId"];
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Email;
        }
        public List<EntityEmail> GetPendingEmails()
        {
            List<EntityEmail> Emails = new List<EntityEmail>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Email]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.Guid, GETMODE.PENDING);
                    SQLServer.AddInParameter(dbCommand, "@Status", DbType.Int32, 0);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Emails;
                            }
                            while (OReader.Read())
                            {
                                EntityEmail Email = new EntityEmail();
                                Email.EmailId = (Guid)OReader["EmailId"];
                                Email.EmailFrom = OReader["EmailFrom"].ToString();
                                Email.EmailToAddress = OReader["EmailToAddress"].ToString();
                                Email.EmailBcc = OReader["EmailBcc"].ToString();
                                Email.EmailCC = OReader["EmailCC"].ToString();
                                Email.EmailSubject = OReader["EmailSubject"].ToString();
                                Email.EmailContent = OReader["EmailContent"].ToString();
                                Email.EmailType = OReader["EmailType"].ToString();
                                Email.EmailCreatedBy = (Guid)OReader["EmailCreatedBy"];
                                Email.EmailCreatedOn = (DateTime)OReader["EmailCreatedOn"];
                                Email.EmaiStatus = (Int32)OReader["EmaiStatus"];
                                Email.EmailSendOn = (DateTime)OReader["EmailSendOn"];
                                Emails.Add(Email);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Emails;
        }
        public string InsertEmail(EntityEmail Email)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Email]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@EmailFrom", DbType.String, Email.EmailFrom);
                    SQLServer.AddInParameter(dbCommand, "@EmailToAddress", DbType.String, Email.EmailToAddress);
                    SQLServer.AddInParameter(dbCommand, "@EmailBcc", DbType.String, Email.EmailBcc);
                    SQLServer.AddInParameter(dbCommand, "@EmailCC", DbType.String, Email.EmailCC);
                    SQLServer.AddInParameter(dbCommand, "@EmailSubject", DbType.String, Email.EmailSubject);
                    SQLServer.AddInParameter(dbCommand, "@EmailContent", DbType.String, Email.EmailContent);
                    SQLServer.AddInParameter(dbCommand, "@EmailType", DbType.String, Email.EmailType);
                    SQLServer.AddInParameter(dbCommand, "@EmailCreatedBy", DbType.Guid, Email.EmailCreatedBy);
                    SQLServer.AddInParameter(dbCommand, "@EmailCreatedOn", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@EmaiStatus", DbType.Int32,0);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateEmail(EntityEmail Email)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Email]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@EmailId", DbType.Guid, Email.EmailId);
                    SQLServer.AddInParameter(dbCommand, "@EmailSendOn", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@EmaiStatus", DbType.Int32, Email.EmaiStatus);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string ActivateUserAccount(Guid userId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[U_UserEmailStatus]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@UserId", DbType.Guid, userId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = (string)SQLServer.ExecuteScalar(dbCommand);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        #endregion
    }
}

